<?php

namespace app\controllers;

use Yii;
use yii\web\Controller;
use yii\web\NotFoundHttpException;

require dirname(dirname(__FILE__)).'/excel/PHPExcel.php';

class ImportdataController extends Controller
{
	private $userid;
	private $usertype;

	public function beforeAction($action)
	{
		if(!(isset($_SESSION['cms_userid']) && $_SESSION['cms_userid'] != null))
		{
			return $this->redirect(['admin/login']);
		}
		if ($_SESSION['cms_usertype'] == 1) {
			throw new NotFoundHttpException("没有权限");
			
		}
		$this->userid = $_SESSION['cms_userid'];
		$this->usertype = $_SESSION['cms_usertype'];
		return true;
	}

	public function actionIndex()
	{
		$msg = '';
		if (isset($_GET['msg'])) {
			$msg = $_GET['msg'];
		}
		return $this->render("index",['msg' => $msg]);
	}

	/*
	* 说明：
	* 1、导入文件格式必须为Microsoft Excel
	* 2、文件内容不能为空，而且表格行数必须大于2(第一行为标题)，列数必须大于2(第一列为X轴)
	*/
	public function actionAdd()
	{
		$files = $_FILES;
		$errorFileName = array();
		
		if(!empty($files))
		{
			$connection = Yii::$app->db;
			
			foreach ($files as $key => $file) {
				if($file['error'] == 0)
				{
					$filename = $filedesc = $file['name'];
					if(isset($_POST[$key."_name"]) && $_POST[$key."_name"] != "")
					{
						$filename = $_POST[$key."_name"];
					}
					if(isset($_POST[$key."_desc"]) && $_POST[$key."_desc"] != "")
					{
						$filedesc = $_POST[$key."_desc"];
					}

					$sqlData = ['excelname' => $filename,'exceldescription'=>$filedesc];
					$connection->createCommand()->insert("tbl_excel",$sqlData)->execute();
					$id = $connection->createCommand("SELECT last_insert_id() as id")->queryOne();
					$id = $id['id'];
					
					$flag = $this->importData($file,$id);
					if(!$flag)
					{
						$errorFileName[] = $file['name'];
						//导入数据失败，删除tbl_excel里的记录
						$connection->createCommand()->delete("tbl_excel",'id = '.$id)->execute();
					}
				}
			}
		}		
		if (empty($errorFileName)) {
			$msg = "数据导入成功";
		}
		else
		{
			$msg = "文件 ".implode("，", $errorFileName)." 导入失败";
		}
		return $this->redirect(["index",'msg' => $msg]);
	}

	protected function importData($file,$excelid)
	{
		$objReader = \PHPExcel_IOFactory::createReader("Excel2007");//默认excel2007及以上版本
		if (!$objReader->canRead($file['tmp_name'])) {    
			$objReader = \PHPExcel_IOFactory::createReader("Excel5");  //格式不对，用excel2003读取
			
			if (!$objReader->canRead($file['tmp_name'])) {
				return false;  //不是ms excel文件
			}
			
		}
		$objPHPExcel = $objReader->load($file['tmp_name']);
		
		$data = $objPHPExcel->getActiveSheet()->toArray('',false,true,false);//按行转换为数组
		
		//判断是否为空数组
		if(empty($data))
		{
			return false;
		}

		$titles = $data[0]; //第一行标题
		$colnum = count($titles); //列数

		unset($data[0]);
		//判断除去标题之后，是否为空数组
		if(empty($data))
		{
			return false;
		}

		//转换成按列的数组
		$arr = array();
		foreach ($data as $row) {
			foreach ($row as $colIndex => $cellValue) {
				$arr[$colIndex][] = $cellValue;
			}
		}

		$xArr = $arr[0];  //x轴
		unset($arr[0]);
		//判断去掉x轴之后，是否为空数组
		if(empty($arr))
		{
			return false;
		}

		$connection = Yii::$app->db;

		foreach ($arr as $colkey => $colValue) {
			$connection->createCommand()->insert("tbl_exceldata",[
				'excelid' => $excelid,
				'xAxisData' => implode(",", $xArr),
				'yAxisData' => implode(",", $colValue),
				'xAxisDescription' => $titles[0],
				'yAxisDescription' => $titles[$colkey]
			])->execute();
		}

		return true;

	}

}